package com.blog.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import com.blog.dto.Article_tag;
import com.blog.util.DataAccess;
import com.blog.util.DateTime;

public class Article_tagDao {
	
	/**
	 *根据标签id和文章ID(Article_tag at)插入关系
	 * @param tag_id
	 * @param article_id
	 * @return
	 */
	public boolean addArticle_tag(Article_tag at) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		String sql = "insert into article_tag values(null,?,?,null)";
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1,at.getTag_id());
			ps.setInt(2, at.getArticle_id());
			if(ps.executeUpdate() == 1) flag=true;	
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!插入失败");
			e.printStackTrace();
			return false;
		}finally {
			DataAccess.getInstance().closeConnection( con , ps);
		}
		return flag;
	}
	
	/**
	 *根据标签id和文章ID(Article_tag at)插入关系
	 * @param tag_id
	 * @param article_id
	 * @return
	 */
	public boolean addArticle_tag(int user_id,int article_id ,String tag) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		String sql = "insert into article_tag(tag_id , article_id)  " + 
				"select  " + 
				"	(select tag_id from tag where user_id = ? AND tag_name = ? AND delete_at is NULL ) as tag_id,  " + 
				"	? as article_id ;" ;
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, user_id);
			ps.setString(2, tag);
			ps.setInt(3, article_id);
			if(ps.executeUpdate() == 1) flag=true;	
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!插入失败");
			e.printStackTrace();
			return false;
		}finally {
			DataAccess.getInstance().closeConnection( con , ps);
		}
		return flag;
	}
	
	
	/**
	 *根据用户id，标签名和最后的文章ID插入关系
	 * @param tag_id
	 * @param article_id
	 * @return
	 */
	public boolean addLastArticle_tag(int user_id ,String tag) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		String sql = "insert into article_tag(tag_id , article_id)  " + 
				"select  " + 
				"	(select tag_id from tag where user_id = ? AND tag_name = ? AND delete_at is NULL ) as tag_id,  " + 
				"	( select article_id from article where article_id=(select max(article_id) from article) ) as article_id ;" ;
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, user_id);
			ps.setString(2, tag);
			if(ps.executeUpdate() == 1) flag=true;	
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!插入失败");
			e.printStackTrace();
			return false;
		}finally {
			DataAccess.getInstance().closeConnection( con , ps);
		}
		return flag;
	}
	
	
	/**
	 * 根据对象Article_tag at修改内容
	 * @param at
	 * @return
	 */
	public boolean editArticle_tag(Article_tag at) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		String sql = "update article_tag set tag_id = ? , article_id = ?  where  at_id = ? AND delete_at is NULL";
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1,at.getTag_id());
			ps.setInt(2, at.getArticle_id());
			ps.setInt(3, at.getAt_id());
			if(ps.executeUpdate() == 1) flag=true;	
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!插入失败");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection( con , ps);
		}
		return flag;
	}
	
	/**
	 * 删除文章与标签的关系
	 * @param at
	 * @return
	 */
	public boolean delArticle_tag(Article_tag at) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		String sql = "update article_tag set delete_at = ?  where  at_id = ? AND delete_at is NULL";
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(2, at.getAt_id());
			ps.setTimestamp(1, DateTime.getDateTime());
			if(ps.executeUpdate() == 1) flag=true;	
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!插入失败");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection( con , ps);
		}
		return flag;
	}
	
	/**
	 * 根据id删除文章与标签的关系
	 * @param at_id
	 * @return
	 */
	public boolean delArticle_tagById(int at_id) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		String sql = "update article_tag set delete_at = ?  where  at_id = ? AND delete_at is NULL";
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(2, at_id);
			ps.setTimestamp(1, DateTime.getDateTime());
			if(ps.executeUpdate() == 1) flag=true;	
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!插入失败");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection( con , ps);
		}
		return flag;
	}
	
	/**
	 * 根据文章id文章与标签的关系
	 * @param at_id
	 * @return
	 */
	public boolean delArticle_tagByArtId(int art_id) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		String sql = "update article_tag set delete_at = ?  where  article_id = ? AND delete_at is NULL";
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(2, art_id);
			ps.setTimestamp(1, DateTime.getDateTime());
			if(ps.executeUpdate() == 1) flag=true;	
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!插入失败");
			e.printStackTrace();
			return false;
		}finally {
			DataAccess.getInstance().closeConnection( con , ps);
		}
		return flag;
	}
	
	/**
	 * 根据文章id文章与标签的关系,硬删除
	 * @param at_id
	 * @return
	 */
	public boolean delHardArticle_tagByArtId(int art_id) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		String sql = "delete from article_tag where article_id = ? ";
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, art_id);
			if(ps.executeUpdate() == 1) flag=true;	
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!硬删除失败");
			e.printStackTrace();
			return false;
		}finally {
			DataAccess.getInstance().closeConnection( con , ps);
		}
		return flag;
	}
	
	
	/**
	 * 根据标签id和文章ID查找关系
	 * @param tag_id
	 * @param article_id
	 * @return
	 */
	public boolean findArticle_tagByTwoId(int tag_id , int article_id ) {
		boolean flag = false ;
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null;
		String sql = " select * from article_tag  where tag_id = ? AND article_id = ? AND  delete_at  is  NULL  ";
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, tag_id);
			ps.setInt(2, article_id);
			rs = ps.executeQuery();
			if (rs.next()) flag = true;
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection(rs, ps, con);
		}
		return flag;
	}
	
	/**
	 * 根据文章ID查找相关标签关系
	 * @param at_id
	 * @return
	 */
	public Vector<Article_tag> findArticle_tagByArticle_id(int article_id) {
		Vector<Article_tag> v= new Vector<Article_tag>();
		Connection con = DataAccess.getInstance().getConnection();
		PreparedStatement ps = null ;
		ResultSet rs = null;
		String sql = " select * from article_tag  where article_id = ? AND  delete_at  is  NULL  ";
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, article_id);
			rs = ps.executeQuery();
			while(rs.next()) {
				Article_tag aTag  = new Article_tag();
				aTag.setArticle_id(rs.getInt("article_id"));
				aTag.setAt_id(rs.getInt("at_id"));
				aTag.setTag_id(rs.getInt("tag_id"));
				v.add(aTag);
			}
		} catch (SQLException e) {
			System.err.println("sql语句执行异常!");
			e.printStackTrace();
		}finally {
			DataAccess.getInstance().closeConnection(rs, ps, con);
		}
		return v;
	}
	
	
	
}
